﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using BioModel;
using System.Runtime.Serialization;

namespace BioDAL
{
    [DataContract]
    public class FilmDAL : IFilmDAL
    {
        #region Connection
        private SqlConnection sqlCn = null;

        public FilmDAL()
        {
        }
        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region GetMaxID
        public int GetMaxId(string kolonneNavn, string tabel)
        {
            try
            {
                int mId = -1;
                string sql = "Select MAX(" + kolonneNavn + ") as maxID from " + tabel;
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(String.Format("{0}", dr["maxID"]));
                        mId = (int)(dr["maxID"]);
                    }
                    dr.Close();
                }
                return mId;
            }
            catch (SqlException se2)
            {
                Console.WriteLine("!SQL fejl " + se2);
                return -1;
            }
        }
        #endregion

        #region Film
        public int InsertFilm(string titel, string instruktoer, string beskrivelse, string genre, string laengde, bool aktiv, string billede, bool er3D, double billetPris)
        {
            try
            {
                string sql = string.Format("Insert into bioFilm" +
                     "(titel,beskrivelse,genre,laengde, aktiv, instruktoer,billede, er3D, billetPris) Values" +
                     "('{0}', '{1}', '{2}', '{3}','{4}','{5}','{6}','{7}','{8}')", titel, beskrivelse, genre, laengde, aktiv, instruktoer, billede, er3D, billetPris);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {
                Console.Write("!SQL Insert Film error " + se);

            }
            return GetMaxId("FilmID", "bioFilm");
        }

        public void addKunstnerTilFilm(int filmID, List<string> kunstner)
        {
            try
            {
                foreach (string a in kunstner)
                {
                    string sql = string.Format("Insert into bioKunstner (filmID,kunstnerNavn) Values ({0},'{1}')", filmID, a);
                    using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException se)
            {
                Console.Write("!SQL Insert Artist error " + se);

            }
        }

        public void EditFilm(int id, string titel, string beskrivelse, string genre, string filmLaengde)
        {
            string sql = string.Format("Update bioFilm Set titel = '{0}', beskrivelse = '{1}', genre = '{2}', filmLaengde = '{3}') Where filmID = {4}"
                , titel, beskrivelse, genre, filmLaengde, id);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public List<Film> AlleStatusFilm(bool state)
        {
            List<Film> aktivFilmList = new List<Film>();
            string sql = string.Format("Select * From bioFilm where aktiv = '{0}'", state);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    aktivFilmList.Add(BuildFilm(dr));
                }
                dr.Close();
            }
            return aktivFilmList;

        }

        public List<Film> AlleFilm()
        {
            List<Film> filmList = new List<Film>();
            string sql = string.Format("Select * From bioFilm");
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    filmList.Add(BuildFilm(dr));

                    //ARTISTER KOMMER IKKE MED I MOVIE::: FIKS :)
                }
                dr.Close();
            }
            return filmList;
        }

        public Film GetEnFilm(int filmID)
        {
            Film _movie = new Film();
            string sql = string.Format("Select * from bioFilm where filmID = {0}", filmID);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    _movie = BuildFilm(dr);
                }
                dr.Close();
            }
            return _movie;
        }

        public Film GetEnFilmFraTitel(string titel)
        {
            Film _movie = new Film();
            string sql = string.Format("Select * from bioFilm where titel = '{0}'", titel);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    _movie = BuildFilm(dr);
                }
                dr.Close();
            }
            return _movie;
        }

        private static Film BuildFilm(SqlDataReader dr)
        {
            return new Film(
                                    (int)(dr["filmID"]),
                                    (double)(dr["billetPris"]),
                                    (string)(dr["titel"]),
                                    (string)(dr["beskrivelse"]),
                                    (string)(dr["genre"]),
                                    (string)(dr["laengde"]),
                                    (string)(dr["instruktoer"]),
                                    (string)(dr["billede"]),
                                    (bool)(dr["aktiv"]),
                                    (bool)(dr["er3D"])
                                    );
        }

        public void SetFilmStatus(int filmID, bool status)
        {
            string sql = string.Format("Update bioFilm Set (aktiv = {0}) where filmID = {1}", status, filmID);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }
        #endregion
    }
}
